﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    // NOTE: If you change the class name "Header" here, you must also update the reference to "Header" in App.config.
    public partial class MainService : IHeader
    {
        public int Header_Using(int DocumentID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Document_ID", DocumentID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Header_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Header_GetList(out int ItemCount, int currentPage, int pageSize, string _BranchCode, int _Month, int _Year, string _Type)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            dbCmd.Parameters.AddWithValue("@Month", _Month);
            dbCmd.Parameters.AddWithValue("@Year", _Year);
            dbCmd.Parameters.AddWithValue("@Type", _Type);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public HeaderInfo Header_GetInfo(int HeaderID)
        {
            HeaderInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Document_ID", HeaderID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new HeaderInfo();
                    retVal.ID = Convert.ToInt32(dr["Document_ID"]);
                    retVal.BranhCode = dr["Document_BranchCode"].ToString();
                    retVal.Date = dr["Document_Date"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Document_Date"].ToString());
                    retVal.Month = Convert.ToInt32(dr["Document_Month"]);
                    retVal.Year = Convert.ToInt32(dr["Document_Year"]);
                    retVal.Period = Convert.ToInt32(dr["Document_Period"]);
                    retVal.Social = Convert.ToDecimal(dr["Document_Social"]);
                    retVal.Medical = Convert.ToDecimal(dr["Document_Medical"]);
                    retVal.Unemployed = Convert.ToDecimal(dr["Document_Unemployed"]);
                    retVal.BasicSalary = Convert.ToDecimal(dr["Document_BasicSalary"]);
                    retVal.IsInitial = Convert.ToBoolean(dr["Document_IsInitial"]);
                    retVal.Type = dr["Document_Type"].ToString();
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public HeaderInfo Header_GetMaxBymonth(int _Month, int _Year, string _BranchCode, string _Type)
        {
            HeaderInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_GetMaxByMonth", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Document_Month", _Month);
            dbCmd.Parameters.AddWithValue("@Document_Year", _Year);
            dbCmd.Parameters.AddWithValue("@Document_BranchCode", _BranchCode);
            dbCmd.Parameters.AddWithValue("@Document_Type", _Type);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new HeaderInfo();
                    retVal.ID = Convert.ToInt32(dr["Document_ID"]);
                    retVal.BranhCode = dr["Document_BranchCode"].ToString();
                    retVal.Date = dr["Document_Date"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Document_Date"].ToString());
                    retVal.Month = Convert.ToInt32(dr["Document_Month"]);
                    retVal.Year = Convert.ToInt32(dr["Document_Year"]);
                    retVal.Period = Convert.ToInt32(dr["Document_Period"]);
                    retVal.Social = Convert.ToDecimal(dr["Document_Social"]);
                    retVal.Medical = Convert.ToDecimal(dr["Document_Medical"]);
                    retVal.Unemployed = Convert.ToDecimal(dr["Document_Unemployed"]);
                    retVal.BasicSalary = Convert.ToDecimal(dr["Document_BasicSalary"]);
                    retVal.IsInitial = Convert.ToBoolean(dr["Document_IsInitial"]);
                    retVal.Type = dr["Document_Type"].ToString();
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Header_Insert(HeaderInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Document_BranchCode", eInfo.BranhCode);
            dbCmd.Parameters.AddWithValue("@Document_Date", eInfo.Date);
            dbCmd.Parameters.AddWithValue("@Document_Month", eInfo.Month);
            dbCmd.Parameters.AddWithValue("@Document_Year", eInfo.Year);
            dbCmd.Parameters.AddWithValue("@Document_Period", eInfo.Period);
            dbCmd.Parameters.AddWithValue("@Document_Social", eInfo.Social);
            dbCmd.Parameters.AddWithValue("@Document_Medical", eInfo.Medical);
            dbCmd.Parameters.AddWithValue("@Document_Unemployed", eInfo.Unemployed);
            dbCmd.Parameters.AddWithValue("@Document_BasicSalary", eInfo.BasicSalary);
            dbCmd.Parameters.AddWithValue("@Document_IsInitial", eInfo.IsInitial);
            dbCmd.Parameters.AddWithValue("@Document_Type", eInfo.Type);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Header_Update(HeaderInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Document_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@Document_BranchCode", eInfo.BranhCode);
            dbCmd.Parameters.AddWithValue("@Document_Date", eInfo.Date);
            dbCmd.Parameters.AddWithValue("@Document_Month", eInfo.Month);
            dbCmd.Parameters.AddWithValue("@Document_Year", eInfo.Year);
            dbCmd.Parameters.AddWithValue("@Document_Period", eInfo.Period);
            dbCmd.Parameters.AddWithValue("@Document_Social", eInfo.Social);
            dbCmd.Parameters.AddWithValue("@Document_Medical", eInfo.Medical);
            dbCmd.Parameters.AddWithValue("@Document_Unemployed", eInfo.Unemployed);
            dbCmd.Parameters.AddWithValue("@Document_BasicSalary", eInfo.BasicSalary);
            dbCmd.Parameters.AddWithValue("@Document_IsInitial", eInfo.IsInitial);
            dbCmd.Parameters.AddWithValue("@Document_Type", eInfo.Type);
            try
            {
                dbConn.Open();
                //if (dbCmd.ExecuteNonQuery() > 0)
                //{
                //    retVal = true;
                //}
                dbCmd.ExecuteNonQuery();
                retVal = true;
            }
            catch (Exception ex)
            {
                retVal = false;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Header_Delete(int DocumentID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Document_ID", DocumentID);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Header_DeleteByPeriod(string BrandCode, int Year, int Month, int Period, string Type)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_DeleteByPeriod", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BrandCode", BrandCode);
            dbCmd.Parameters.AddWithValue("@Year", Year);
            dbCmd.Parameters.AddWithValue("@Month", Month);
            dbCmd.Parameters.AddWithValue("@Period", Period);
            dbCmd.Parameters.AddWithValue("@Type", Type);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public int Header_GetPreDocumentPeroid(string BrandCode, int Year, int Month, int Period, string Type)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_GetPreDocumentPeriod", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchCode", BrandCode);
            dbCmd.Parameters.AddWithValue("@Year", Year);
            dbCmd.Parameters.AddWithValue("@Month", Month);
            dbCmd.Parameters.AddWithValue("@Period", Period);
            dbCmd.Parameters.AddWithValue("@Document_Type", Type);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Header_Exist(int _ID, string BranchCode, DateTime _Date, string _SIBookNo, string _FromDate, string _ToDate, string _InsTypeCode)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_Exist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _ID);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@Date", _Date);
            dbCmd.Parameters.AddWithValue("@InsBookNo", _SIBookNo);
            dbCmd.Parameters.AddWithValue("@FromDate", _FromDate);
            dbCmd.Parameters.AddWithValue("@ToDate", _ToDate);
            dbCmd.Parameters.AddWithValue("@InsTypeCode", _InsTypeCode);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Header_CheckExist(int _ID, string BranchCode, DateTime _Date, int _Month, int _Year, int _Period,string _Type)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Header_CheckExist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _ID);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@Date", _Date);
            dbCmd.Parameters.AddWithValue("@Month", _Month);
            dbCmd.Parameters.AddWithValue("@Year", _Year);
            dbCmd.Parameters.AddWithValue("@Period", _Period);
            dbCmd.Parameters.AddWithValue("@Document_Type", _Type);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }


        
    }
}
